﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ClassModel;
using System.Data.SqlClient;
using System.Data;

namespace ClassDAL
{
    /// <summary>
    /// 课程信息 Data Access Layer
    /// </summary>
    public class t_class_course_DAL
    {
        /// <summary>
        /// 插入课程信息
        /// </summary>
        /// <param name="courese"></param>
        /// <returns></returns>
        public static int InsertCoures(IList<t_class_course> courese)
        {
            StringBuilder insertBuilder = new StringBuilder();

            courese.ToList().ForEach(course =>
            {
                insertBuilder.AppendLine(string.Format("insert into t_class_course values('{0}',{1},'{2}',(select ISNULL(MAX(dataTxtid),0) + 1  from t_class_course where fileId = {3}));",
                        course.coursename,
                        course.fileid,
                        course.createtime.ToString("yyyy-MM-dd HH:mm:ss"),
                        course.fileid
                    ));
            });
               
            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertBuilder.ToString());
        }

        /// <summary>
        /// 获取某个(fileid) Excel 表格里的所有课程信息
        /// </summary>
        /// <param name="fileid"></param>
        /// <returns></returns>
        public static List<t_class_course> GetCourseListWithFileID(int fileid)
        {
            string selectSQL = "select id,coursename,fileid,createtime,dataTxtid from t_class_course where fileid = @fileid order by datatxtid asc;";

            SqlParameter selectParam = new SqlParameter()
            {
                ParameterName = "@fileid",
                Value = fileid
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam);
            if (ds == null || ds.Tables.Count == 0) return null;

            DataRowCollection rows = ds.Tables[0].Rows;
            List<t_class_course> result = new List<t_class_course>();
            foreach (DataRow row in rows)
            {
                t_class_course course = new t_class_course();
                course.id = Convert.ToInt32(row["id"]);
                course.coursename = row["coursename"].ToString();
                course.fileid = Convert.ToInt32(row["fileid"]);
                course.createtime = Convert.ToDateTime(row["createtime"]);
                course.dataTxtid = Convert.ToInt32(row["dataTxtid"]);// 算法需要的id
                result.Add(course);
            }

            return result;
        }

        /// <summary>
        /// 根据excel的fileid获取课程数量
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public static int getCourseCountWithFileId(int fileId)
        {
            string getCountSQL = "select COUNT(1) from t_class_course where fileId = @fileid;";

            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), CommandType.Text, getCountSQL, new SqlParameter() { ParameterName = @"fileid", Value = fileId }));
        }


        /// <summary>
        /// 根据课程id，获取课程信息
        /// </summary>
        /// <param name="id">课程id</param>
        /// <returns></returns>
        public static t_class_course GetCourseById(int id)
        {
            string selectSQL = "select id,coursename,fileid,createtime,datatxtid from t_class_course where id = @id;";
            SqlParameter selectParam = new SqlParameter() 
            {
                ParameterName = "@id",
                Value = id
            };

            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam);
            if (!reader.Read()) return null;

            t_class_course course = new t_class_course();
            course.id = Convert.ToInt32(reader["id"]);
            course.coursename = reader["coursename"].ToString();
            course.fileid = Convert.ToInt32(reader["fileid"]);
            course.createtime = Convert.ToDateTime(reader["createtime"]);
            course.dataTxtid = Convert.ToInt32(reader["datatxtid"]);

            reader.Close();

            return course;
        }
    }
}
